﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using Maticsoft.DBUtility;//Please add references

using Gift.Model;
namespace Gift.DAL
{
    /// <summary>
    /// 数据访问类:tbGoods
    /// </summary>
    public partial class DALGoods
    {
        public DALGoods()
        { }
        #region  Method

        /// <summary>
        /// 得到最大ID
        /// </summary>
        public int GetMaxId()
        {
            return DbHelperSQL.GetMaxID("goodsId", "tbGoods");
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int goodsId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from tbGoods");
            strSql.Append(" where goodsId=@goodsId ");
            SqlParameter[] parameters = {
					new SqlParameter("@goodsId", SqlDbType.Int,4)			};
            parameters[0].Value = goodsId;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool ExistsGoodsCardType(int goodsId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from tbGoods g join tbGoodsCardType gct on g.goodsId = gct.goodsId join tbCardType ct on gct.cardTypeId=ct.Id and isnull(ct.disable,0)=0 ");
            strSql.Append(" where g.goodsId=@goodsId ");
            SqlParameter[] parameters = {
					new SqlParameter("@goodsId", SqlDbType.Int,4)			};
            parameters[0].Value = goodsId;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }



        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(Gift.Model.tbGoods model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into tbGoods(");
            strSql.Append("goodsName,goodsCode,typeId,brandId,makePoints,changePoints,isPromotion,bigPic,smallPic,goodsContent,goodsCount,isSale,prokey,visible,contenten,updatetime,addTime,clicks,goodsOrder,instruction,mobileInstruction,addAdminId,isRush,rushPoints,rushTimeFrom,rushTimeTo,allowAllCard,isRecom,termsId,detailUrl,isModelType,deliveryTemplateId,goodsPrice)");
            strSql.Append(" values (");
            strSql.Append("@goodsName,@goodsCode,@typeId,@brandId,@makePoints,@changePoints,@isPromotion,@bigPic,@smallPic,@goodsContent,@goodsCount,@isSale,@prokey,@visible,@contenten,@updatetime,@addTime,@clicks,@goodsOrder,@instruction,@mobileInstruction,@addAdminId,@isRush,@rushPoints,@rushTimeFrom,@rushTimeTo,@allowAllCard,@isRecom,@termsId,@detailUrl,@isModelType,@deliveryTemplateId,@goodsPrice)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@goodsName", SqlDbType.NVarChar,250),
					new SqlParameter("@goodsCode", SqlDbType.NVarChar,150),
					new SqlParameter("@typeId", SqlDbType.Int,4),
					new SqlParameter("@brandId", SqlDbType.Int,4),
					new SqlParameter("@makePoints", SqlDbType.Int,4),
					new SqlParameter("@changePoints", SqlDbType.Int,4),
					new SqlParameter("@isPromotion", SqlDbType.Bit,1),
					new SqlParameter("@bigPic", SqlDbType.NVarChar,150),
					new SqlParameter("@smallPic", SqlDbType.NVarChar,150),
					new SqlParameter("@goodsContent", SqlDbType.NVarChar),
					new SqlParameter("@goodsCount", SqlDbType.Int,4),
					new SqlParameter("@isSale", SqlDbType.Bit,1),
					new SqlParameter("@prokey", SqlDbType.NVarChar,250),
					new SqlParameter("@visible", SqlDbType.Bit,1),
					new SqlParameter("@contenten", SqlDbType.NVarChar,2000),
					new SqlParameter("@updatetime", SqlDbType.DateTime),
					new SqlParameter("@addTime", SqlDbType.DateTime),
					new SqlParameter("@clicks", SqlDbType.Int,4),
					new SqlParameter("@goodsOrder", SqlDbType.Int,4),
					new SqlParameter("@instruction", SqlDbType.NVarChar,1000),
					new SqlParameter("@mobileInstruction", SqlDbType.NVarChar,500),
					new SqlParameter("@addAdminId", SqlDbType.Int,4),
					new SqlParameter("@isRush", SqlDbType.Bit,1),
					new SqlParameter("@rushPoints", SqlDbType.Int,4),
					new SqlParameter("@rushTimeFrom", SqlDbType.DateTime),
					new SqlParameter("@rushTimeTo", SqlDbType.DateTime),
                    new SqlParameter("@allowAllCard", SqlDbType.Bit,1),
                    new SqlParameter("@isRecom", SqlDbType.Bit,1),
                    new SqlParameter("@termsId", SqlDbType.Int,4),
                      new SqlParameter("@detailUrl", SqlDbType.NVarChar,300),
                         new SqlParameter("@isModelType", SqlDbType.Bit,1),
                           new SqlParameter("@deliveryTemplateId", SqlDbType.Int,4),
                           new SqlParameter("@goodsPrice", SqlDbType.Decimal,4)
                         
                    
                                        };
            parameters[0].Value = model.goodsName;
            parameters[1].Value = model.goodsCode;
            parameters[2].Value = model.typeId;
            parameters[3].Value = model.brandId;
            parameters[4].Value = model.makePoints;
            parameters[5].Value = model.changePoints;
            parameters[6].Value = model.isPromotion;
            parameters[7].Value = model.bigPic;
            parameters[8].Value = model.smallPic;
            parameters[9].Value = model.goodsContent;
            parameters[10].Value = model.goodsCount;
            parameters[11].Value = model.isSale;
            parameters[12].Value = model.prokey;
            parameters[13].Value = model.visible;
            parameters[14].Value = model.contenten;
            parameters[15].Value = model.updatetime;
            parameters[16].Value = model.addTime;
            parameters[17].Value = model.clicks;
            parameters[18].Value = model.goodsOrder;
            parameters[19].Value = model.instruction;
            parameters[20].Value = model.mobileInstruction;
            parameters[21].Value = model.addAdminId;
            parameters[22].Value = model.isRush;
            parameters[23].Value = model.rushPoints;
            parameters[24].Value = model.rushTimeFrom;
            parameters[25].Value = model.rushTimeTo;
            parameters[26].Value = model.AllowAllCard;
            parameters[27].Value = model.IsRecom;
            parameters[28].Value = model.termsId;
            parameters[29].Value = model.DetailUrl;
            parameters[30].Value = model.IsModelType;
            parameters[31].Value = model.DeliveryTemplateId;
            parameters[32].Value = model.goodsPrice;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Gift.Model.tbGoods model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update tbGoods set ");
            strSql.Append("goodsName=@goodsName,");
            strSql.Append("goodsCode=@goodsCode,");
            strSql.Append("typeId=@typeId,");
            strSql.Append("brandId=@brandId,");
            strSql.Append("makePoints=@makePoints,");
            strSql.Append("changePoints=@changePoints,");
            strSql.Append("isPromotion=@isPromotion,");
            strSql.Append("bigPic=@bigPic,");
            strSql.Append("smallPic=@smallPic,");
            strSql.Append("goodsContent=@goodsContent,");
            strSql.Append("goodsCount=@goodsCount,");
            strSql.Append("isSale=@isSale,");
            strSql.Append("prokey=@prokey,");
            strSql.Append("visible=@visible,");
            strSql.Append("contenten=@contenten,");
            strSql.Append("updatetime=@updatetime,");
            strSql.Append("addTime=@addTime,");
            strSql.Append("clicks=@clicks,");
            strSql.Append("goodsOrder=@goodsOrder,");
            strSql.Append("instruction=@instruction,");
            strSql.Append("mobileInstruction=@mobileInstruction,");
            strSql.Append("addAdminId=@addAdminId,");
            strSql.Append("isRush=@isRush,");
            strSql.Append("rushPoints=@rushPoints,");
            strSql.Append("rushTimeFrom=@rushTimeFrom,");
            strSql.Append("rushTimeTo=@rushTimeTo,");
            strSql.Append("allowAllCard=@allowAllCard,");
            strSql.Append("isRecom=@isRecom,");
            strSql.Append("termsId=@termsId,");
            strSql.Append("detailUrl=@detailUrl,");
            strSql.Append("isModelType=@isModelType,");
            strSql.Append("deliveryTemplateId = @deliveryTemplateId, ");
            strSql.Append("goodsPrice = @goodsPrice ");
            strSql.Append(" where goodsId=@goodsId");
            SqlParameter[] parameters = {
					new SqlParameter("@goodsName", SqlDbType.NVarChar,250),
					new SqlParameter("@goodsCode", SqlDbType.NVarChar,150),
					new SqlParameter("@typeId", SqlDbType.Int,4),
					new SqlParameter("@brandId", SqlDbType.Int,4),
					new SqlParameter("@makePoints", SqlDbType.Int,4),
					new SqlParameter("@changePoints", SqlDbType.Int,4),
					new SqlParameter("@isPromotion", SqlDbType.Bit,1),
					new SqlParameter("@bigPic", SqlDbType.NVarChar,150),
					new SqlParameter("@smallPic", SqlDbType.NVarChar,150),
					new SqlParameter("@goodsContent", SqlDbType.NVarChar),
					new SqlParameter("@goodsCount", SqlDbType.Int,4),
					new SqlParameter("@isSale", SqlDbType.Bit,1),
					new SqlParameter("@prokey", SqlDbType.NVarChar,250),
					new SqlParameter("@visible", SqlDbType.Bit,1),
					new SqlParameter("@contenten", SqlDbType.NVarChar,2000),
					new SqlParameter("@updatetime", SqlDbType.DateTime),
					new SqlParameter("@addTime", SqlDbType.DateTime),
					new SqlParameter("@clicks", SqlDbType.Int,4),
					new SqlParameter("@goodsOrder", SqlDbType.Int,4),
					new SqlParameter("@instruction", SqlDbType.NVarChar,1000),
					new SqlParameter("@mobileInstruction", SqlDbType.NVarChar,500),
					new SqlParameter("@addAdminId", SqlDbType.Int,4),
					new SqlParameter("@isRush", SqlDbType.Bit,1),
					new SqlParameter("@rushPoints", SqlDbType.Int,4),
					new SqlParameter("@rushTimeFrom", SqlDbType.DateTime),
					new SqlParameter("@rushTimeTo", SqlDbType.DateTime),
					new SqlParameter("@goodsId", SqlDbType.Int,4),
                    new SqlParameter("@allowAllCard", SqlDbType.Bit,1),
                    new SqlParameter("@isRecom", SqlDbType.Bit,1),
                    new SqlParameter("@termsId", SqlDbType.Int,4),
                      new SqlParameter("@detailUrl", SqlDbType.NVarChar,300),
                         new SqlParameter("@isModelType", SqlDbType.Bit,1),
                         new SqlParameter("@deliveryTemplateId",SqlDbType.Int,4),
                                         new SqlParameter("@goodsPrice",SqlDbType.Decimal,4)
                                        
                                        };
            parameters[0].Value = model.goodsName;
            parameters[1].Value = model.goodsCode;
            parameters[2].Value = model.typeId;
            parameters[3].Value = model.brandId;
            parameters[4].Value = model.makePoints;
            parameters[5].Value = model.changePoints;
            parameters[6].Value = model.isPromotion;
            parameters[7].Value = model.bigPic;
            parameters[8].Value = model.smallPic;
            parameters[9].Value = model.goodsContent;
            parameters[10].Value = model.goodsCount;
            parameters[11].Value = model.isSale;
            parameters[12].Value = model.prokey;
            parameters[13].Value = model.visible;
            parameters[14].Value = model.contenten;
            parameters[15].Value = model.updatetime;
            parameters[16].Value = model.addTime;
            parameters[17].Value = model.clicks;
            parameters[18].Value = model.goodsOrder;
            parameters[19].Value = model.instruction;
            parameters[20].Value = model.mobileInstruction;
            parameters[21].Value = model.addAdminId;
            parameters[22].Value = model.isRush;
            parameters[23].Value = model.rushPoints;
            parameters[24].Value = model.rushTimeFrom;
            parameters[25].Value = model.rushTimeTo;
            parameters[26].Value = model.goodsId;
            parameters[27].Value = model.AllowAllCard;
            parameters[28].Value = model.IsRecom;
            parameters[29].Value = model.termsId;
            parameters[30].Value = model.DetailUrl;
                parameters[31].Value = model.IsModelType;
                parameters[32].Value = model.DeliveryTemplateId;
                parameters[33].Value = model.goodsPrice;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int goodsId)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("update tbGoods set visible = 0 ");
            strSql.Append(" where goodsId=@goodsId ");
            SqlParameter[] parameters = {
					new SqlParameter("@goodsId", SqlDbType.Int,4)			};
            parameters[0].Value = goodsId;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }



        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Clear(int goodsId)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from tbGoods  ");
            strSql.Append(" where goodsId=@goodsId ");
            SqlParameter[] parameters = {
					new SqlParameter("@goodsId", SqlDbType.Int,4)			};
            parameters[0].Value = goodsId;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string goodsIdlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update tbGoods set visible = 0 ");
            strSql.Append(" where goodsId in (" + goodsIdlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public bool DeleteListFromStore(string goodsIdlist)
        {
           
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from  tbGoods ");
            strSql.Append(" where goodsId in (" + goodsIdlist + ") ; ");
            strSql.AppendFormat("DELETE FROM DB_Shoppic where proid in ({0});", goodsIdlist);
            strSql.AppendFormat("DELETE FROM DB_proshu where proid in ({0});", goodsIdlist);
            strSql.AppendFormat("DELETE FROM DB_proxian where proid in ({0});", goodsIdlist);
            strSql.AppendFormat("DELETE FROM DB_propei where proid in ({0});", goodsIdlist);
            strSql.AppendFormat("DELETE FROM DB_pronew where proid in ({0});", goodsIdlist);
            strSql.AppendFormat("DELETE FROM DB_sale where proid in ({0});", goodsIdlist);
            strSql.AppendFormat("DELETE FROM tbComment where proid in ({0});", goodsIdlist);
            strSql.AppendFormat("DELETE FROM DB_topicpro where proid in ({0});", goodsIdlist);
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public bool DeleteListRestore(string goodsIdlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update tbGoods set  visible=1 ");
            strSql.Append(" where goodsId in (" + goodsIdlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 批量更新上下架
        /// </summary>
        public bool BranchUpdateSale(string goodsIdlist,int isSale)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update tbGoods set isSale = @isSale ");
            strSql.Append(" where goodsId in (" + goodsIdlist + ")  ");

            SqlParameter[] parameters = {
					new SqlParameter("@isSale", SqlDbType.Int,4)			};
            parameters[0].Value = isSale;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }

        }

        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool ClearList(string goodsIdlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from tbGoods  ");
            strSql.Append(" where goodsId in (" + goodsIdlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Gift.Model.tbGoods GetModelByGoodsCode(string sGoodsCode)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 goodsId,goodsName,goodsCode,typeId,brandId,makePoints,changePoints,isPromotion,bigPic,smallPic,goodsContent,goodsCount,isSale,prokey,visible,contenten,updatetime,addTime,clicks,goodsOrder,instruction,mobileInstruction,addAdminId,isRush,rushPoints,rushTimeFrom,rushTimeTo,allowAllCard,isRecom from tbGoods ");
            strSql.Append(" where goodsCode=@goodsCode");
            SqlParameter[] parameters = {
					new SqlParameter("@goodsCode", SqlDbType.VarChar,50)
			};
            parameters[0].Value = sGoodsCode;

            Gift.Model.tbGoods model = new Gift.Model.tbGoods();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["goodsId"] != null && ds.Tables[0].Rows[0]["goodsId"].ToString() != "")
                {
                    model.goodsId = int.Parse(ds.Tables[0].Rows[0]["goodsId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["goodsName"] != null && ds.Tables[0].Rows[0]["goodsName"].ToString() != "")
                {
                    model.goodsName = ds.Tables[0].Rows[0]["goodsName"].ToString();
                }
                if (ds.Tables[0].Rows[0]["goodsCode"] != null && ds.Tables[0].Rows[0]["goodsCode"].ToString() != "")
                {
                    model.goodsCode = ds.Tables[0].Rows[0]["goodsCode"].ToString();
                }
                if (ds.Tables[0].Rows[0]["typeId"] != null && ds.Tables[0].Rows[0]["typeId"].ToString() != "")
                {
                    model.typeId = int.Parse(ds.Tables[0].Rows[0]["typeId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["brandId"] != null && ds.Tables[0].Rows[0]["brandId"].ToString() != "")
                {
                    model.brandId = int.Parse(ds.Tables[0].Rows[0]["brandId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["makePoints"] != null && ds.Tables[0].Rows[0]["makePoints"].ToString() != "")
                {
                    model.makePoints = int.Parse(ds.Tables[0].Rows[0]["makePoints"].ToString());
                }
                if (ds.Tables[0].Rows[0]["changePoints"] != null && ds.Tables[0].Rows[0]["changePoints"].ToString() != "")
                {
                    model.changePoints = int.Parse(ds.Tables[0].Rows[0]["changePoints"].ToString());
                }
                if (ds.Tables[0].Rows[0]["isPromotion"] != null && ds.Tables[0].Rows[0]["isPromotion"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["isPromotion"].ToString() == "1") || (ds.Tables[0].Rows[0]["isPromotion"].ToString().ToLower() == "true"))
                    {
                        model.isPromotion = true;
                    }
                    else
                    {
                        model.isPromotion = false;
                    }
                }
                if (ds.Tables[0].Rows[0]["bigPic"] != null && ds.Tables[0].Rows[0]["bigPic"].ToString() != "")
                {
                    model.bigPic = ds.Tables[0].Rows[0]["bigPic"].ToString();
                }
                if (ds.Tables[0].Rows[0]["smallPic"] != null && ds.Tables[0].Rows[0]["smallPic"].ToString() != "")
                {
                    model.smallPic = ds.Tables[0].Rows[0]["smallPic"].ToString();
                }
                if (ds.Tables[0].Rows[0]["goodsContent"] != null && ds.Tables[0].Rows[0]["goodsContent"].ToString() != "")
                {
                    model.goodsContent = ds.Tables[0].Rows[0]["goodsContent"].ToString();
                }
                if (ds.Tables[0].Rows[0]["goodsCount"] != null && ds.Tables[0].Rows[0]["goodsCount"].ToString() != "")
                {
                    model.goodsCount = int.Parse(ds.Tables[0].Rows[0]["goodsCount"].ToString());
                }
                if (ds.Tables[0].Rows[0]["isSale"] != null && ds.Tables[0].Rows[0]["isSale"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["isSale"].ToString() == "1") || (ds.Tables[0].Rows[0]["isSale"].ToString().ToLower() == "true"))
                    {
                        model.isSale = true;
                    }
                    else
                    {
                        model.isSale = false;
                    }
                }
                if (ds.Tables[0].Rows[0]["isRecom"] != null && ds.Tables[0].Rows[0]["isRecom"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["isRecom"].ToString() == "1") || (ds.Tables[0].Rows[0]["isRecom"].ToString().ToLower() == "true"))
                    {
                        model.IsRecom = true;
                    }
                    else
                    {
                        model.IsRecom = false;
                    }
                }
                if (ds.Tables[0].Rows[0]["allowAllCard"] != null && ds.Tables[0].Rows[0]["allowAllCard"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["allowAllCard"].ToString() == "1") || (ds.Tables[0].Rows[0]["allowAllCard"].ToString().ToLower() == "true"))
                    {
                        model.AllowAllCard = true;
                    }
                    else
                    {
                        model.AllowAllCard = false;
                    }
                }


                if (ds.Tables[0].Rows[0]["prokey"] != null && ds.Tables[0].Rows[0]["prokey"].ToString() != "")
                {
                    model.prokey = ds.Tables[0].Rows[0]["prokey"].ToString();
                }
                if (ds.Tables[0].Rows[0]["visible"] != null && ds.Tables[0].Rows[0]["visible"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["visible"].ToString() == "1") || (ds.Tables[0].Rows[0]["visible"].ToString().ToLower() == "true"))
                    {
                        model.visible = true;
                    }
                    else
                    {
                        model.visible = false;
                    }
                }
                if (ds.Tables[0].Rows[0]["contenten"] != null && ds.Tables[0].Rows[0]["contenten"].ToString() != "")
                {
                    model.contenten = ds.Tables[0].Rows[0]["contenten"].ToString();
                }
                if (ds.Tables[0].Rows[0]["updatetime"] != null && ds.Tables[0].Rows[0]["updatetime"].ToString() != "")
                {
                    model.updatetime = DateTime.Parse(ds.Tables[0].Rows[0]["updatetime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["addTime"] != null && ds.Tables[0].Rows[0]["addTime"].ToString() != "")
                {
                    model.addTime = DateTime.Parse(ds.Tables[0].Rows[0]["addTime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["clicks"] != null && ds.Tables[0].Rows[0]["clicks"].ToString() != "")
                {
                    model.clicks = int.Parse(ds.Tables[0].Rows[0]["clicks"].ToString());
                }
                if (ds.Tables[0].Rows[0]["goodsOrder"] != null && ds.Tables[0].Rows[0]["goodsOrder"].ToString() != "")
                {
                    model.goodsOrder = int.Parse(ds.Tables[0].Rows[0]["goodsOrder"].ToString());
                }
                if (ds.Tables[0].Rows[0]["instruction"] != null && ds.Tables[0].Rows[0]["instruction"].ToString() != "")
                {
                    model.instruction = ds.Tables[0].Rows[0]["instruction"].ToString();
                }
                if (ds.Tables[0].Rows[0]["mobileInstruction"] != null && ds.Tables[0].Rows[0]["mobileInstruction"].ToString() != "")
                {
                    model.mobileInstruction = ds.Tables[0].Rows[0]["mobileInstruction"].ToString();
                }
                if (ds.Tables[0].Rows[0]["addAdminId"] != null && ds.Tables[0].Rows[0]["addAdminId"].ToString() != "")
                {
                    model.addAdminId = int.Parse(ds.Tables[0].Rows[0]["addAdminId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["isRush"] != null && ds.Tables[0].Rows[0]["isRush"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["isRush"].ToString() == "1") || (ds.Tables[0].Rows[0]["isRush"].ToString().ToLower() == "true"))
                    {
                        model.isRush = true;
                    }
                    else
                    {
                        model.isRush = false;
                    }
                }
                if (ds.Tables[0].Rows[0]["rushPoints"] != null && ds.Tables[0].Rows[0]["rushPoints"].ToString() != "")
                {
                    model.rushPoints = int.Parse(ds.Tables[0].Rows[0]["rushPoints"].ToString());
                }
                if (ds.Tables[0].Rows[0]["rushTimeFrom"] != null && ds.Tables[0].Rows[0]["rushTimeFrom"].ToString() != "")
                {
                    model.rushTimeFrom = DateTime.Parse(ds.Tables[0].Rows[0]["rushTimeFrom"].ToString());
                }
                if (ds.Tables[0].Rows[0]["rushTimeTo"] != null && ds.Tables[0].Rows[0]["rushTimeTo"].ToString() != "")
                {
                    model.rushTimeTo = DateTime.Parse(ds.Tables[0].Rows[0]["rushTimeTo"].ToString());
                }
                return model;
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Gift.Model.tbGoods GetModel(int goodsId)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 goodsId,goodsName,goodsCode,typeId,brandId,makePoints,changePoints,isPromotion,bigPic,smallPic,goodsContent,goodsCount,isSale,prokey,visible,contenten,updatetime,addTime,clicks,goodsOrder,instruction,mobileInstruction,addAdminId,isRush,rushPoints,rushTimeFrom,rushTimeTo,allowAllCard,isRecom,termsId,isnull(detailUrl,'') as detailUrl,isModelType,isnull(deliveryTemplateId,0) as deliveryTemplateId, isnull(goodsPrice,0) as goodsPrice from tbGoods ");
            strSql.Append(" where goodsId=@goodsId");
            SqlParameter[] parameters = {
					new SqlParameter("@goodsId", SqlDbType.Int,4)
			};
            parameters[0].Value = goodsId;

            Gift.Model.tbGoods model = new Gift.Model.tbGoods();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["goodsId"] != null && ds.Tables[0].Rows[0]["goodsId"].ToString() != "")
                {
                    model.goodsId = int.Parse(ds.Tables[0].Rows[0]["goodsId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["goodsName"] != null && ds.Tables[0].Rows[0]["goodsName"].ToString() != "")
                {
                    model.goodsName = ds.Tables[0].Rows[0]["goodsName"].ToString();
                }
                if (ds.Tables[0].Rows[0]["goodsCode"] != null && ds.Tables[0].Rows[0]["goodsCode"].ToString() != "")
                {
                    model.goodsCode = ds.Tables[0].Rows[0]["goodsCode"].ToString();
                }
                if (ds.Tables[0].Rows[0]["typeId"] != null && ds.Tables[0].Rows[0]["typeId"].ToString() != "")
                {
                    model.typeId = int.Parse(ds.Tables[0].Rows[0]["typeId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["brandId"] != null && ds.Tables[0].Rows[0]["brandId"].ToString() != "")
                {
                    model.brandId = int.Parse(ds.Tables[0].Rows[0]["brandId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["termsId"] != null && ds.Tables[0].Rows[0]["termsId"].ToString() != "")
                {
                    model.termsId = int.Parse(ds.Tables[0].Rows[0]["termsId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["makePoints"] != null && ds.Tables[0].Rows[0]["makePoints"].ToString() != "")
                {
                    model.makePoints = int.Parse(ds.Tables[0].Rows[0]["makePoints"].ToString());
                }
                if (ds.Tables[0].Rows[0]["changePoints"] != null && ds.Tables[0].Rows[0]["changePoints"].ToString() != "")
                {
                    model.changePoints = int.Parse(ds.Tables[0].Rows[0]["changePoints"].ToString());
                }
                if (ds.Tables[0].Rows[0]["isPromotion"] != null && ds.Tables[0].Rows[0]["isPromotion"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["isPromotion"].ToString() == "1") || (ds.Tables[0].Rows[0]["isPromotion"].ToString().ToLower() == "true"))
                    {
                        model.isPromotion = true;
                    }
                    else
                    {
                        model.isPromotion = false;
                    }
                }
                if (ds.Tables[0].Rows[0]["bigPic"] != null && ds.Tables[0].Rows[0]["bigPic"].ToString() != "")
                {
                    model.bigPic = ds.Tables[0].Rows[0]["bigPic"].ToString();
                }
                if (ds.Tables[0].Rows[0]["smallPic"] != null && ds.Tables[0].Rows[0]["smallPic"].ToString() != "")
                {
                    model.smallPic = ds.Tables[0].Rows[0]["smallPic"].ToString();
                }
                if (ds.Tables[0].Rows[0]["goodsContent"] != null && ds.Tables[0].Rows[0]["goodsContent"].ToString() != "")
                {
                    model.goodsContent = ds.Tables[0].Rows[0]["goodsContent"].ToString();
                }
                if (ds.Tables[0].Rows[0]["goodsCount"] != null && ds.Tables[0].Rows[0]["goodsCount"].ToString() != "")
                {
                    model.goodsCount = int.Parse(ds.Tables[0].Rows[0]["goodsCount"].ToString());
                }
                if (ds.Tables[0].Rows[0]["isSale"] != null && ds.Tables[0].Rows[0]["isSale"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["isSale"].ToString() == "1") || (ds.Tables[0].Rows[0]["isSale"].ToString().ToLower() == "true"))
                    {
                        model.isSale = true;
                    }
                    else
                    {
                        model.isSale = false;
                    }
                }
                if (ds.Tables[0].Rows[0]["isRecom"] != null && ds.Tables[0].Rows[0]["isRecom"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["isRecom"].ToString() == "1") || (ds.Tables[0].Rows[0]["isRecom"].ToString().ToLower() == "true"))
                    {
                        model.IsRecom = true;
                    }
                    else
                    {
                        model.IsRecom = false;
                    }
                }
                if (ds.Tables[0].Rows[0]["allowAllCard"] != null && ds.Tables[0].Rows[0]["allowAllCard"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["allowAllCard"].ToString() == "1") || (ds.Tables[0].Rows[0]["allowAllCard"].ToString().ToLower() == "true"))
                    {
                        model.AllowAllCard = true;
                    }
                    else
                    {
                        model.AllowAllCard = false;
                    }
                }

                
                if (ds.Tables[0].Rows[0]["prokey"] != null && ds.Tables[0].Rows[0]["prokey"].ToString() != "")
                {
                    model.prokey = ds.Tables[0].Rows[0]["prokey"].ToString();
                }
                if (ds.Tables[0].Rows[0]["visible"] != null && ds.Tables[0].Rows[0]["visible"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["visible"].ToString() == "1") || (ds.Tables[0].Rows[0]["visible"].ToString().ToLower() == "true"))
                    {
                        model.visible = true;
                    }
                    else
                    {
                        model.visible = false;
                    }
                }
                if (ds.Tables[0].Rows[0]["contenten"] != null && ds.Tables[0].Rows[0]["contenten"].ToString() != "")
                {
                    model.contenten = ds.Tables[0].Rows[0]["contenten"].ToString();
                }
                if (ds.Tables[0].Rows[0]["updatetime"] != null && ds.Tables[0].Rows[0]["updatetime"].ToString() != "")
                {
                    model.updatetime = DateTime.Parse(ds.Tables[0].Rows[0]["updatetime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["addTime"] != null && ds.Tables[0].Rows[0]["addTime"].ToString() != "")
                {
                    model.addTime = DateTime.Parse(ds.Tables[0].Rows[0]["addTime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["clicks"] != null && ds.Tables[0].Rows[0]["clicks"].ToString() != "")
                {
                    model.clicks = int.Parse(ds.Tables[0].Rows[0]["clicks"].ToString());
                }
                if (ds.Tables[0].Rows[0]["goodsOrder"] != null && ds.Tables[0].Rows[0]["goodsOrder"].ToString() != "")
                {
                    model.goodsOrder = int.Parse(ds.Tables[0].Rows[0]["goodsOrder"].ToString());
                }
                if (ds.Tables[0].Rows[0]["instruction"] != null && ds.Tables[0].Rows[0]["instruction"].ToString() != "")
                {
                    model.instruction = ds.Tables[0].Rows[0]["instruction"].ToString();
                }
                if (ds.Tables[0].Rows[0]["mobileInstruction"] != null && ds.Tables[0].Rows[0]["mobileInstruction"].ToString() != "")
                {
                    model.mobileInstruction = ds.Tables[0].Rows[0]["mobileInstruction"].ToString();
                }
                if (ds.Tables[0].Rows[0]["addAdminId"] != null && ds.Tables[0].Rows[0]["addAdminId"].ToString() != "")
                {
                    model.addAdminId = int.Parse(ds.Tables[0].Rows[0]["addAdminId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["isRush"] != null && ds.Tables[0].Rows[0]["isRush"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["isRush"].ToString() == "1") || (ds.Tables[0].Rows[0]["isRush"].ToString().ToLower() == "true"))
                    {
                        model.isRush = true;
                    }
                    else
                    {
                        model.isRush = false;
                    }
                }
                if (ds.Tables[0].Rows[0]["rushPoints"] != null && ds.Tables[0].Rows[0]["rushPoints"].ToString() != "")
                {
                    model.rushPoints = int.Parse(ds.Tables[0].Rows[0]["rushPoints"].ToString());
                }
                if (ds.Tables[0].Rows[0]["rushTimeFrom"] != null && ds.Tables[0].Rows[0]["rushTimeFrom"].ToString() != "")
                {
                    model.rushTimeFrom = DateTime.Parse(ds.Tables[0].Rows[0]["rushTimeFrom"].ToString());
                }
                if (ds.Tables[0].Rows[0]["rushTimeTo"] != null && ds.Tables[0].Rows[0]["rushTimeTo"].ToString() != "")
                {
                    model.rushTimeTo = DateTime.Parse(ds.Tables[0].Rows[0]["rushTimeTo"].ToString());
                }
                model.DetailUrl = ds.Tables[0].Rows[0]["detailUrl"].ToString();

                if (ds.Tables[0].Rows[0]["isModelType"] != null && ds.Tables[0].Rows[0]["isModelType"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["isModelType"].ToString() == "1") || (ds.Tables[0].Rows[0]["isModelType"].ToString().ToLower() == "true"))
                    {
                        model.IsModelType = true;
                    }
                    else
                    {
                        model.IsModelType = false;
                    }
                }
                model.DeliveryTemplateId = int.Parse(ds.Tables[0].Rows[0]["deliveryTemplateId"].ToString());

                if (ds.Tables[0].Rows[0]["goodsPrice"] != null && ds.Tables[0].Rows[0]["goodsPrice"].ToString() != "")
                {
                    model.goodsPrice = decimal.Parse(ds.Tables[0].Rows[0]["goodsPrice"].ToString());
                }

                return model;
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select goodsId,goodsName,goodsCode,typeId,brandId,makePoints,changePoints,isPromotion,bigPic,smallPic,goodsContent,goodsCount,isSale,prokey,visible,contenten,updatetime,addTime,clicks,goodsOrder,instruction,mobileInstruction,addAdminId,isRush,isnull(detailUrl,'') as detailUrl ");
            strSql.Append(" FROM tbGoods ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// iGoodsType 只为顶级类别
        /// </summary>
        public DataSet GetList(int Top, int iGoodsType)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" goodsId,goodsName,goodsCode,typeId,brandId,makePoints,changePoints,isPromotion,bigPic,smallPic,goodsContent,goodsCount,isSale,prokey,visible,contenten,updatetime,addTime,clicks,goodsOrder,instruction,mobileInstruction,addAdminId,isRush ");
            strSql.Append(" FROM tbGoods where isnull(visible,0)=1 ");

            if (iGoodsType > 0)
            {
                strSql.AppendFormat(" and typeId in (select typeId from tbGoodsType where parentId in (select typeId from tbGoodsType where parentId={0}))"
                                    , iGoodsType);
            }

            strSql.Append(" order by saleCount desc ");
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// iGoodsType 只为顶级类别
        /// </summary>
        public DataSet GetRecommList(int Top, int iTypeId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" goodsId,goodsName,goodsCode,typeId,brandId,makePoints,changePoints,isPromotion,bigPic,smallPic,goodsContent,goodsCount,isSale,prokey,visible,contenten,updatetime,addTime,clicks,goodsOrder,instruction,mobileInstruction,addAdminId,isRush ");
            strSql.Append(" FROM tbGoods where isnull(visible,0)=1 and  isnull(isRecom,0) =1 ");

            if (iTypeId > 0)
            {
                strSql.AppendFormat(" and typeId in (select typeId from tbGoodsType where parentId in (select typeId from tbGoodsType where parentid = {0}))", iTypeId);
            }


            strSql.Append(" order by goodsOrder desc ");
            return DbHelperSQL.Query(strSql.ToString());
        }

        public DataSet GetRelationList(int iGoodsId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            strSql.Append(" goodsId,goodsName,goodsCode,typeId,brandId,makePoints,changePoints,isPromotion,bigPic,smallPic,goodsContent,goodsCount,isSale,prokey,visible,contenten,updatetime,addTime,clicks,goodsOrder,instruction,mobileInstruction,addAdminId,isRush ");
            strSql.Append(" FROM tbGoods where isnull(visible,0)=1 and  goodsId in (select proIds from DB_Proxian where proId = @goodsId) ");



            strSql.Append(" order by goodsOrder desc ");

            SqlParameter[] parameters = {
					new SqlParameter("@goodsId", SqlDbType.Int,4)			};
            parameters[0].Value = iGoodsId;
            return DbHelperSQL.Query(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM tbGoods where visible = 1 ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" and " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT distinct goodsId, goodsName, goodsCode, typeId, brandId, termsId,makePoints, changePoints,costPrice, isPromotion, bigPic, smallPic, goodsContent, goodsCount, isnull(isSale,0) as isSale, prokey, visible, isnull(modelTypeId,0) as modelTypeId,isnull(modelTypeName,'') as modelTypeName, ");
            strSql.Append("(select top 1 brandName from tbBrand where brandId=TT.brandId) as brandName,");
            strSql.Append("(select top 1 termsName from tbTerms where termsId=TT.termsId) as termsName,");
            strSql.Append("isnull((select sum(isnull(goodsCount,0)) from tbWarehouseGoods where goodsId = TT.goodsId and isnull(modelTypeId,0) = isnull(TT.modelTypeId,0) ),0) as realyCount,");
            strSql.Append("isnull((select sum(isnull(freezeCount,0)) from tbGoodsFreeze where goodsId = TT.goodsId  and isnull(modelTypeId,0) = isnull(TT.modelTypeId,0)),0) as freezeCount,");
            strSql.Append(" isnull((select sum(isnull(goodsCount,0)) from tbWarehouseGoods where goodsId = TT.goodsId  and isnull(modelTypeId,0) = isnull(TT.modelTypeId,0)),0) - isnull((select sum(isnull(freezeCount,0)) from tbGoodsFreeze where goodsId = TT.goodsId  and isnull(modelTypeId,0) = isnull(TT.modelTypeId,0)),0) as ableCount,");
            strSql.Append(" contenten, updatetime, addTime, clicks, goodsOrder, instruction, mobileInstruction,costPrice, addAdminId,isnull(isRush,0) as isRush, rushPoints, rushTimeFrom, rushTimeTo, isnull(allowAllCard,0) as allowAllCard,isnull(isRecom,0) as isRecom,");
            strSql.Append(" case when isnull(isRush,0) =1 and getdate() between rushTimeFrom and rushTimeTo then rushPoints ");
            strSql.Append("  when isnull(TT.modelTypeId,0) != 0 then (select goodsPoints from tbModelType where modelTypeId = TT.modelTypeId)    else changePoints end as points    FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.goodsId desc");
            }
            strSql.Append(")AS Row, T.*,mt.modelTypeId,mt.modelTypeName,isnull(T.goodsPrice,0) as costPrice  from tbGoods T left join tbModelType mt on T.goodsId = mt.goodsId left join tbWarehouseGoods wg on T.goodsId=wg.goodsId and mt.modelTypeId=wg.modelTypeId where isnull(visible,0) = 1 ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" and  " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /*
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetList(int PageSize,int PageIndex,string strWhere)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@tblName", SqlDbType.VarChar, 255),
                    new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                    new SqlParameter("@PageSize", SqlDbType.Int),
                    new SqlParameter("@PageIndex", SqlDbType.Int),
                    new SqlParameter("@IsReCount", SqlDbType.Bit),
                    new SqlParameter("@OrderType", SqlDbType.Bit),
                    new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
                    };
            parameters[0].Value = "tbGoods";
            parameters[1].Value = "goodsId";
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 0;
            parameters[6].Value = strWhere;	
            return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
        }*/



        public DataSet GetDataListPage(string sTypeId, string sSubTypeId, string sOrder, string sArrowDirectionSale, string sArrowDirectionPrice, int iBeginIndex, int iEndIndex, int iCardTypeId, string sCardPrice, string keywords,bool isExchange,int iTermsId,int? iPointsFrom,int? iPointsTo,int iBid)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append("SELECT goodsId,goodsName,changePoints,bigPic,smallPic,typeid,isnull(goodsCount,0) as goodsCount,goodsCode,"
                          + " saleCount,saleTime,(select isnull(count(1),0) from tbGoodsCardType gct join tbCardType ct on gct.cardTypeId=ct.Id and gct.goodsId=tbGoods.goodsId) as cardTypeCount,allowAllCard "
            + " FROM tbGoods where visible=1 and isnull(isSale,0)=1 and isnull(isRush,0)=0   ");
            sbSql.Append(" and isnull(goodsCount,0)>0 ");
            if (isExchange)
            {
                sbSql.Append(" and goodsId in(select gt.goodsId from tbGoodsCardType gt join tbCardType ct on gt.cardTypeId = ct.Id and isnull(ct.Disable,0)=0) ");
            }

            if (iCardTypeId >= 0)
            {
                sbSql.AppendFormat(" and changePoints in ({0}) ", sCardPrice);
            }
            if (!string.IsNullOrEmpty(keywords))
            {
                sbSql.AppendFormat(" and (goodsName like '%{0}%' or Instruction like '%{0}%' or goodsContent like '%{0}%') ", keywords);
            }
            if (!string.IsNullOrEmpty(sTypeId))
            {
                string tempTypeId = "";
                if (string.IsNullOrEmpty(sSubTypeId))
                {
                    tempTypeId = sTypeId;
                }
                else
                {
                    tempTypeId = sSubTypeId;
                }

                tbGoodsType oGoodsType = new DALGoodsType().GetModel(int.Parse(tempTypeId));
                if (oGoodsType.typedepth == 0)
                {
                    sbSql.AppendFormat(" and typeId in (select typeId from tbGoodsType where parentId in(select typeId from tbGoodsType where ParentId={0}))"
                                        , tempTypeId);
                }
                else if (oGoodsType.typedepth == 1)
                {
                    sbSql.AppendFormat(" and typeId in (select typeId from tbGoodsType where ParentId={0})"
                                     , tempTypeId);
                }
                else if (oGoodsType.typedepth == 2)
                {
                    sbSql.AppendFormat(" and typeId={0} "
                                       , tempTypeId
                                       );
                }
            }
            else
            {
                string tempTypeId = "";
                if (!string.IsNullOrEmpty(sSubTypeId))
                {
                    tempTypeId = sSubTypeId;

                    tbGoodsType oGoodsType = new DALGoodsType().GetModel(int.Parse(tempTypeId));
                    if (oGoodsType.typedepth == 0)
                    {
                        sbSql.AppendFormat(" and typeId in (select typeId from tbGoodsType where parentId in(select typeId from tbGoodsType where ParentId={0}))"
                                            , tempTypeId);
                    }
                    else if (oGoodsType.typedepth == 1)
                    {
                        sbSql.AppendFormat(" and typeId in (select typeId from tbGoodsType where ParentId={0})"
                                         , tempTypeId);
                    }
                    else if (oGoodsType.typedepth == 2)
                    {
                        sbSql.AppendFormat(" and typeId={0} "
                                           , tempTypeId
                                           );
                    }
                }

              
            }

            if (iTermsId > 0)
            {
                sbSql.AppendFormat(" and termsId={0}", iTermsId);
            }
            if (iPointsFrom > 0)
            {

                sbSql.AppendFormat(" and changePoints >={0} "
                                     , iPointsFrom);

            }
            if (iPointsTo > 0)
            {

                sbSql.AppendFormat(" and changePoints <={0} "
                                     , iPointsTo);
            }
            if (iBid > 0)
            {
                sbSql.AppendFormat(" and brandId = {0} "
                                   , iBid);
            }


            string sOrderBy = string.Empty;
            if (string.IsNullOrEmpty(sOrder) || sOrder == "sale")
            {
                sOrderBy = " order by saleCount {0} ";
                if (sArrowDirectionSale == "&darr;")
                {
                    sOrderBy = string.Format(sOrderBy, "DESC");
                }
                else
                {
                    sOrderBy = string.Format(sOrderBy, "ASC");
                }
            }
            else if (sOrder == "price")
            {
                sOrderBy = " order by changePoints {0} ";
                if (sArrowDirectionPrice == "&darr;")
                {
                    sOrderBy = string.Format(sOrderBy, "DESC");
                }
                else
                {
                    sOrderBy = string.Format(sOrderBy, "ASC");
                }
            }
            else if (sOrder == "time")
            {
                sOrderBy = " order by saleTime desc ";
            }


            StringBuilder sbPage = new StringBuilder();

            sbPage.Append("select * from (");
            sbPage.Append("select row_number() over(" + sOrderBy + ") AS RowNumber, goodsId,goodsCode,goodsName,changePoints,goodsCount,typeid,bigPic,cardTypeCount,allowAllCard ");
            sbPage.Append(" FROM (");
            sbPage.Append(sbSql.ToString());
            sbPage.Append(")a ");
            sbPage.Append(")b ");
            sbPage.Append(" where RowNumber >= " + iBeginIndex.ToString() + " and RowNumber < " + iEndIndex.ToString());

            StringBuilder sbRecordCount = new StringBuilder();
            sbRecordCount.Append("select count(0) as RecordCount");
            sbRecordCount.Append(" FROM (");
            sbRecordCount.Append(sbSql.ToString());
            sbRecordCount.Append(")a ");

            DataSet dsCount = DbHelperSQL.Query(sbRecordCount.ToString());

            sbSql.Append(sOrderBy);

            DataSet ds = DbHelperSQL.Query(sbPage.ToString());
            if (ds != null)
            {
                DataTable dtTemp = new DataTable();
                dtTemp.Columns.Add(new DataColumn("RecordCount", Type.GetType("System.String")));
                DataRow drTemp;

                foreach (DataRow dr in dsCount.Tables[0].Rows)
                {
                    drTemp = dtTemp.NewRow();
                    drTemp[0] = dr[0];
                    dtTemp.Rows.Add(drTemp);
                }
                ds.Tables.Add(dtTemp);
                return ds;
            }
            else
            {
                return null;
            }
        }


        public DataSet GetDataListPageForRushing( string sOrder, string sArrowDirectionSale, string sArrowDirectionPrice, int iBeginIndex, int iEndIndex, string keywords)
        {
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append("SELECT goodsId,goodsName,changePoints,rushPoints,rushTimeFrom,rushTimeTo,bigPic,smallPic,typeid,isnull(goodsCount,0) as goodsCount,goodsCode,"
                          + " saleCount,saleTime "
            + " FROM tbGoods where visible=1 and isnull(isSale,0)=1 and isnull(isRush,0)=1 and isnull(allowAllCard,0)=1 and  getdate() between rushTimeFrom and rushTimeTo and isnull(rushPoints,0)>0  ");
            sbSql.Append(" and isnull(goodsCount,0)>0 ");

            if (!string.IsNullOrEmpty(keywords))
            {
                sbSql.AppendFormat(" and (goodsName like '%{0}%' or Instruction like '%{0}%' or goodsContent like '%{0}%') ", keywords);
            }

      
            string sOrderBy = string.Empty;
            if (string.IsNullOrEmpty(sOrder) || sOrder == "sale")
            {
                sOrderBy = " order by saleCount {0} ";
                if (sArrowDirectionSale == "&darr;")
                {
                    sOrderBy = string.Format(sOrderBy, "DESC");
                }
                else
                {
                    sOrderBy = string.Format(sOrderBy, "ASC");
                }
            }
            else if (sOrder == "price")
            {
                sOrderBy = " order by rushPoints {0} ";
                if (sArrowDirectionPrice == "&darr;")
                {
                    sOrderBy = string.Format(sOrderBy, "DESC");
                }
                else
                {
                    sOrderBy = string.Format(sOrderBy, "ASC");
                }
            }
            else if (sOrder == "time")
            {
                sOrderBy = " order by rushTimeTo desc ";
            }


            StringBuilder sbPage = new StringBuilder();

            sbPage.Append("select * from (");
            sbPage.Append("select row_number() over(" + sOrderBy + ") AS RowNumber, goodsId,goodsCode,goodsName,changePoints,rushPoints,rushTimeFrom,rushTimeTo,goodsCount,typeid,bigPic ");
            sbPage.Append(" FROM (");
            sbPage.Append(sbSql.ToString());
            sbPage.Append(")a ");
            sbPage.Append(")b ");
            sbPage.Append(" where RowNumber > " + iBeginIndex.ToString() + " and RowNumber <= " + iEndIndex.ToString());

            StringBuilder sbRecordCount = new StringBuilder();
            sbRecordCount.Append("select count(0) as RecordCount");
            sbRecordCount.Append(" FROM (");
            sbRecordCount.Append(sbSql.ToString());
            sbRecordCount.Append(")a ");

            DataSet dsCount = DbHelperSQL.Query(sbRecordCount.ToString());

            sbSql.Append(sOrderBy);

            DataSet ds = DbHelperSQL.Query(sbPage.ToString());
            if (ds != null)
            {
                DataTable dtTemp = new DataTable();
                dtTemp.Columns.Add(new DataColumn("RecordCount", Type.GetType("System.String")));
                DataRow drTemp;

                foreach (DataRow dr in dsCount.Tables[0].Rows)
                {
                    drTemp = dtTemp.NewRow();
                    drTemp[0] = dr[0];
                    dtTemp.Rows.Add(drTemp);
                }
                ds.Tables.Add(dtTemp);
                return ds;
            }
            else
            {
                return null;
            }
        }


        #endregion  Method
    }
}

